package net.wicp.tams.common.binlog.alone.checkpoint;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import lombok.extern.slf4j.Slf4j;
import net.wicp.tams.common.Conf;
import net.wicp.tams.common.apiext.CollectionUtil;
import net.wicp.tams.common.apiext.IOUtil;
import net.wicp.tams.common.apiext.StringUtil;
import net.wicp.tams.common.apiext.jdbc.JdbcAssit;
import net.wicp.tams.common.binlog.alone.ListenerConf.ColHis;
import net.wicp.tams.common.binlog.alone.ListenerConf.ColHis.Builder;
import net.wicp.tams.common.binlog.alone.ListenerConf.ConnConf;
import net.wicp.tams.common.binlog.alone.ListenerConf.Position;
import net.wicp.tams.common.binlog.alone.normalize.ISaveCheckPoint;
import net.wicp.tams.common.constant.dic.YesOrNo;
import net.wicp.tams.common.exception.ExceptAll;
import net.wicp.tams.common.exception.ProjectExceptionRuntime;

@Slf4j
public class CheckPointH2db implements ISaveCheckPoint {
	// private static Server server;
	private Connection connection = null;

	static {
		/*
		 * if (server == null) { try { log.info("正在启动h2..."); server =
		 * Server.createTcpServer(new String[] { "-tcp", "-tcpAllowOthers", "-tcpPort",
		 * "9097" }).start(); log.info("启动成功：" + server.getStatus()); } catch
		 * (SQLException e) { log.error("启动h2出错", e); throw new RuntimeException(e); } }
		 */
	}

	private String url = "";

	@Override
	public void init(ConnConf.Builder connConfBuilder) {
		try {
			Class.forName("org.h2.Driver");
			String databaseName = String.format("%s_%s", connConfBuilder.getIp(), connConfBuilder.getPort());
			// H2默认连接方式就是文件方式,jdbc:h2:~/test 也可以
			// jdbc:h2:tcp://localhost:9097/~/.tams/test
			String dirpath = StringUtil.hasNull(System.getenv("H2_DIR"), Conf.get("common.binlog.alone.h2.dir"));
			url = IOUtil.mergeFolderAndFilePath("jdbc:h2:~/", dirpath,
					databaseName + ";AUTO_RECONNECT=TRUE;AUTO_SERVER=TRUE");
			// url = "jdbc:h2:~/.tams/" + databaseName +
			// ";AUTO_RECONNECT=TRUE;AUTO_SERVER=TRUE";
			connection = DriverManager.getConnection(url, "sa", "");
			Statement stmt = connection.createStatement();
			int executeUpdate = stmt.executeUpdate(
					"CREATE TABLE IF NOT EXISTS `position`  (`gtids` varchar(500) NOT NULL,`fileName` varchar(255) NULL,`pos` long NULL,`masterServerId` long NULL,`time` long NULL,PRIMARY KEY (`gtids`))");

			int executeUpdate2 = stmt.executeUpdate(
					"CREATE TABLE IF NOT EXISTS `colhis`  (`db` varchar(300) NOT NULL,`tb` varchar(300) NOT NULL,`time` long NOT NULL,`cols` varchar(5000) NOT NULL,`coltypes` varchar(3000) NOT NULL,PRIMARY KEY (`db`,`tb`, `time`))");
			stmt.close();
			if (executeUpdate != 0 || executeUpdate2 != 0) {
				throw new ProjectExceptionRuntime(ExceptAll.jdbc_exec_fail, "创建table失败");
			}
			log.info("execute=" + executeUpdate);
		} catch (Exception e) {
			log.error("初始化失败", e);
		}
	}

	@Override
	public void shutdown() {
		try {
			if (connection != null && !connection.isClosed()) {
				connection.close();
			}
		} catch (Exception e) {
			log.error("关闭连接失败", e);
		}
	}

	private PreparedStatement pointPrep;

	@Override
	public void savePoint(Position pos) {
		// replace在1.8以上才支持
		this.pointPrep = checkStmt("replace into position(gtids,filename,pos,masterserverid,time) values(?,?,?,?,?)",
				this.pointPrep);
		try {
			JdbcAssit.setPreParam(pointPrep, pos.getGtids(), pos.getFileName(), pos.getPos(), pos.getMasterServerId(),
					pos.getTime());
			pointPrep.executeUpdate();
			log.info("保存实例:{} 位点{}成功",pos.getServerIp(),pos.getGtids());
		} catch (Exception e) {
			log.error("保存位点失败,实例:" + pos.getServerIp() + " 位点:" + pos.getGtids(), e);
		}
	}

	private PreparedStatement checkStmt(String sql, PreparedStatement stmt) {
		PreparedStatement returnobj = stmt;
		while (true) {
			try {
				if (this.connection == null || this.connection.isClosed()) {
					// 先关闭旧的stmt;
					if (stmt != null && stmt.isClosed()) {
						stmt.close();
					}
					this.connection = DriverManager.getConnection(url, "sa", "");
				}
				if (stmt == null) {
					returnobj = connection.prepareStatement(sql);
				}
				break;
			} catch (Exception e) {
				log.error("数据库连接不上或创建stmt失败", e);
				try {
					Thread.sleep(1000);
				} catch (InterruptedException e1) {
				}
			}
		}
		return returnobj;
	}

	private PreparedStatement colSaveColsPre;

	@Override
	public void saveColName(ColHis colHis) {
		this.colSaveColsPre = checkStmt("replace into colhis(db,tb,time,cols,coltypes) values(?,?,?,?,?)",
				this.colSaveColsPre);
		try {
			String cols = CollectionUtil.listJoin(colHis.getColsList(), ",");
			String colstype = CollectionUtil.listJoin(colHis.getColTypesList(), ",");
			JdbcAssit.setPreParam(this.colSaveColsPre, colHis.getDb(), colHis.getTb(), colHis.getTime(), cols,
					colstype);
			colSaveColsPre.executeUpdate();
			log.info("保存字段名成功,db{},tb{},time{}", colHis.getDb(), colHis.getTb(), colHis.getTime());
		} catch (Exception e) {
			log.error("保存位点失败", e);
		}
	}

	private PreparedStatement queryPointPre;

	@Override
	public Position findPoint(long time) {
		this.queryPointPre = checkStmt("select * from position  where time<=?  order by time desc limit 0,1",
				this.queryPointPre);
		Position retojb = null;
		try {
			JdbcAssit.setPreParam(this.queryPointPre, time);
			ResultSet executeQuery = queryPointPre.executeQuery();
			if (executeQuery.next()) {
				Position.Builder builder = Position.newBuilder();
				builder.setFileName(executeQuery.getString("fileName"));
				builder.setGtids(executeQuery.getString("gtids"));
				builder.setPos(executeQuery.getLong("pos"));
				builder.setMasterServerId(executeQuery.getLong("masterServerId"));
				builder.setTime(executeQuery.getLong("time"));
				retojb = builder.build();
			}
		} catch (SQLException e) {
			log.error("查询位点失败", e);
		}
		return retojb;
	}

	private PreparedStatement queryColsPre;

	@Override
	public List<ColHis> findColsList(String db, String tb) {
		this.queryColsPre = checkStmt("select * from colhis  where db=? and tb=? order by time desc",
				this.queryColsPre);
		List<ColHis> retlist = new ArrayList<>();
		try {
			JdbcAssit.setPreParam(this.queryColsPre, db, tb);
			ResultSet resultSet = queryColsPre.executeQuery();
			while (resultSet.next()) {
				Builder tempBuilder = ColHis.newBuilder();
				tempBuilder.setDb(db);
				tempBuilder.setTb(tb);
				tempBuilder.setTime(resultSet.getLong("time"));
				String[] colsAry = resultSet.getString("cols").split(",");
				tempBuilder.addAllCols(Arrays.asList(colsAry));
				String[] coltypesAry = resultSet.getString("coltypes").split(",");
				tempBuilder.addAllColTypes(Arrays.asList(coltypesAry));
				retlist.add(tempBuilder.build());
			}
			resultSet.close();
		} catch (Exception e) {
			log.error("查colname失败", e);
		}
		return retlist;
	}

	@Override
	public List<ColHis> findColsAll() {
		List<ColHis> retlist = new ArrayList<>();
		try {
			ResultSet resultSet = JdbcAssit.querySql(connection, "select * from colhis order by time desc");
			while (resultSet.next()) {
				Builder tempBuilder = ColHis.newBuilder();
				tempBuilder.setDb(resultSet.getString("db"));
				tempBuilder.setTb(resultSet.getString("tb"));
				tempBuilder.setTime(resultSet.getLong("time"));
				String[] colsAry = resultSet.getString("cols").split(",");
				tempBuilder.addAllCols(Arrays.asList(colsAry));
				String[] coltypesAry = resultSet.getString("coltypes").split(",");
				tempBuilder.addAllColTypes(Arrays.asList(coltypesAry));
				retlist.add(tempBuilder.build());
			}
			resultSet.close();
		} catch (Exception e) {
			log.error("查colname all失败", e);
		}
		return retlist;
	}

	// 不支持分布式锁
	@Override
	public YesOrNo acquireLock() {
		return YesOrNo.yes;
	}

	@Override
	public void releaseLock() {

	}

}
